/*
 * Created on Feb 13, 2005
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.ba360.struts.action;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.ba360.portal.common.PortalConstants;
import com.ba360.portal.database.DBConnectionImpl;
import com.ba360.portal.database.dbo.User;
import com.ba360.portal.util.Formatter;
import com.ba360.struts.form.DealsInfoBean;

/**
 * @author ROnat
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class NewDealsAction extends Action {
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse res) throws Exception {
		// TODO Auto-generated method stub
		String action = request.getParameter(PortalConstants.ACTION);
		HttpSession session = request.getSession();
		User user = (User)session.getAttribute("USER");
		String forward = "success";
		DealsInfoBean dib = (DealsInfoBean)form;
		DBConnectionImpl dbConn = new DBConnectionImpl();
		Connection conn = dbConn.getConnection();
		Statement stmt = conn.createStatement();
		
		if (action==null)
		{
			
			return mapping.findForward("step1");
		}
		else if (action!=null)
		{
			if (action.equals("NewDeal"))
			{
				String category_id = request.getParameter("PostingType");
				String subCatId = request.getParameter("CatType");
				String location_id = request.getParameter("City");
				String placeId = request.getParameter("Place");
				System.out.println("Reinit");
				//dib = new DealsInfoBean();
				dib.setBusinessAddress("");
				dib.setBusinessDesc("");
				dib.setBusinessName("");
				dib.setBusinessPhone("");
				dib.setBusinessType("");
				dib.setTitle("");
				dib.setDealDesc("");
				dib.setDiscountPrice("");
				dib.setEmail("");
				dib.setExpiry("");
				dib.setWebsite("");
				dib.setZip("");
				dib.setCategory_id(category_id);
				dib.setSubCat_id(subCatId);
				dib.setLoc_id(location_id);
				dib.setPlace_id(placeId);
				//} 

				String catName = dLookUp(stmt, "dealscategories", "name", "category_id="
						+ Integer.parseInt(category_id));
				String subCatName = dLookUp(stmt, "dealscategories", "name",
						"category_id=" + Integer.parseInt(subCatId));
				String location = dLookUp(stmt, "lookup_area", "name", "area_id="
						+ Integer.parseInt(location_id));
				String place = dLookUp(stmt, "lookup_area", "name", "area_id="
						+ Integer.parseInt(placeId));

				dib.setMainCatName(catName);
				dib.setSubCatName(subCatName);
				dib.setLocation(location);
				dib.setPlace(place);
				forward = "step2" ;
			}
			if (action.equals("Confirm"))
			{
				return mapping.findForward("step3");
			}
			if (action.equals("Publish"))
			{
				insertIntoTable(dib,conn,session);
				return mapping.findForward("myprofile");
			}
		}
		conn.close();
		return mapping.findForward(forward);
	}
	
	private String dLookUp(java.sql.Statement stat, String table, String fName,
			String where) {
		try {
			java.sql.ResultSet rs = openrs(stat, "SELECT " + fName + " FROM "
					+ table + " WHERE " + where);
			if (!rs.next()) {
				rs.close();
				return "";
			}
			String res = rs.getString(1);
			rs.close();
			return (res == null ? "" : res);
		} catch (Exception e) {
			return "";
		}
	}



	java.sql.ResultSet openrs(java.sql.Statement stat, String sql)
			throws java.sql.SQLException {
		java.sql.ResultSet rs = stat.executeQuery(sql);
		return (rs);
	}
	
	private boolean insertIntoTable(DealsInfoBean dib,Connection conn,HttpSession session) throws Exception
	{
		
		SimpleDateFormat sdf = new SimpleDateFormat(
		"yyyy-MM-dd HH:mm:ss");
		Statement stmt = conn.createStatement();
		String maxId = "";
		ResultSet rs = stmt.executeQuery("select COALESCE(max(business_id),0)+1 from businessinfo");
		
		rs.next();
		maxId = rs.getString(1);
		rs.close();
		
		stmt.close();
		
		stmt = conn.createStatement();
		
		String insStatement =  "insert into businessinfo(business_id,area_id,business_address,business_desc,business_name,business_phone,business_type,business_user_id,email,website,zip) values(";
		
		String values = maxId+","+dib.getPlace_id()+",'"+Formatter.formatString(dib.getBusinessAddress())+"','"+Formatter.formatString(dib.getBusinessDesc())+"','"+Formatter.formatString(dib.getBusinessName())+"','"+dib.getBusinessPhone()+"','"+Formatter.formatString(dib.getBusinessType())+"',"+((User)session.getAttribute("USER")).getUserId()+",'"+dib.getEmail()+"','"+dib.getWebsite()+"','"+dib.getZip()+"')";
		
		//System.out.println(insStatement+values);
		
		stmt.execute(insStatement+values);
		
		stmt.close();
		
		stmt =  conn.createStatement();
		
		insStatement = "insert into businessdeals(user_id,cat_id,date_posted,deal_desc,discount_price,expiry,title,business_id,img) values(";
		
		values = ((User)session.getAttribute("USER")).getUserId()+","+dib.getSubCat_id()+",'" + sdf.format(new Date()) + "','"+Formatter.formatString(dib.getDealDesc())+"','"+Formatter.formatString(dib.getDiscountPrice())+"','"+dib.getExpiry()+"','"+Formatter.formatString(dib.getTitle())+"',"+maxId+",'"+dib.getImg()+"')";
		//System.out.println(insStatement+values);
		stmt.execute(insStatement+values);
		
		stmt.close();
		
		return true;
		
		
		
		
	}
	
}
